//! 数据库存储后端 - 支持 PostgreSQL, MySQL, SQLite

#[cfg(feature = "database")]
use anyhow::Result;
#[cfg(feature = "database")]
use async_trait::async_trait;
#[cfg(feature = "database")]
use chrono::{DateTime, Utc};
#[cfg(feature = "database")]
use good_mitm_passive_scan::{ScanResult, StorageBackend};
#[cfg(feature = "database")]
use log::{debug, error, info};
#[cfg(feature = "database")]
use serde::{Deserialize, Serialize};
#[cfg(feature = "database")]
use sqlx::{any::AnyPoolOptions, Any, Pool};

/// 数据库配置
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DatabaseConfig {
    /// 数据库类型 (postgres, mysql, sqlite)
    pub db_type: String,
    /// 连接字符串
    pub connection_string: String,
    /// 最大连接数
    pub max_connections: u32,
    /// 连接超时（秒）
    pub connection_timeout: u64,
    /// 是否自动创建表
    pub auto_migrate: bool,
}

impl Default for DatabaseConfig {
    fn default() -> Self {
        Self {
            db_type: "sqlite".to_string(),
            connection_string: "sqlite:good_mitm.db".to_string(),
            max_connections: 10,
            connection_timeout: 30,
            auto_migrate: true,
        }
    }
}

/// 数据库存储后端
#[cfg(feature = "database")]
pub struct DatabaseStorage {
    pool: Pool<Any>,
    config: DatabaseConfig,
}

#[cfg(feature = "database")]
impl DatabaseStorage {
    /// 创建新的数据库存储
    pub async fn new(config: DatabaseConfig) -> Result<Self> {
        info!("Initializing database storage: {}", config.db_type);

        let pool = AnyPoolOptions::new()
            .max_connections(config.max_connections)
            .connect(&config.connection_string)
            .await?;

        let storage = Self { pool, config };

        // 自动创建表
        if storage.config.auto_migrate {
            storage.migrate().await?;
        }

        info!("Database storage initialized successfully");

        Ok(storage)
    }

    /// 数据库迁移 - 创建表结构
    async fn migrate(&self) -> Result<()> {
        info!("Running database migrations...");

        // 创建扫描结果表
        let create_table_sql = r#"
            CREATE TABLE IF NOT EXISTS scan_results (
                id TEXT PRIMARY KEY,
                rule_id TEXT NOT NULL,
                rule_name TEXT NOT NULL,
                severity TEXT NOT NULL,
                url TEXT NOT NULL,
                host TEXT NOT NULL,
                path TEXT NOT NULL,
                method TEXT NOT NULL,
                matched_content TEXT,
                evidence TEXT,
                created_at TIMESTAMP NOT NULL,
                metadata TEXT
            )
        "#;

        sqlx::query(create_table_sql).execute(&self.pool).await?;

        // 创建索引
        let create_index_sql = r#"
            CREATE INDEX IF NOT EXISTS idx_scan_results_host ON scan_results(host);
            CREATE INDEX IF NOT EXISTS idx_scan_results_severity ON scan_results(severity);
            CREATE INDEX IF NOT EXISTS idx_scan_results_created_at ON scan_results(created_at);
        "#;

        for sql in create_index_sql.split(';').filter(|s| !s.trim().is_empty()) {
            sqlx::query(sql).execute(&self.pool).await?;
        }

        info!("Database migrations completed");

        Ok(())
    }

    /// 查询扫描结果
    pub async fn query_results(
        &self,
        host: Option<&str>,
        severity: Option<&str>,
        limit: i32,
    ) -> Result<Vec<StoredScanResult>> {
        let mut query = "SELECT * FROM scan_results WHERE 1=1".to_string();

        if let Some(h) = host {
            query.push_str(&format!(" AND host = '{}'", h));
        }

        if let Some(s) = severity {
            query.push_str(&format!(" AND severity = '{}'", s));
        }

        query.push_str(&format!(" ORDER BY created_at DESC LIMIT {}", limit));

        let rows = sqlx::query_as::<_, StoredScanResult>(&query)
            .fetch_all(&self.pool)
            .await?;

        Ok(rows)
    }

    /// 统计扫描结果
    pub async fn get_statistics(&self) -> Result<ScanStatistics> {
        let total: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM scan_results")
            .fetch_one(&self.pool)
            .await?;

        let by_severity: Vec<(String, i64)> = sqlx::query_as(
            "SELECT severity, COUNT(*) FROM scan_results GROUP BY severity",
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(ScanStatistics {
            total_results: total.0 as u64,
            by_severity: by_severity.into_iter().collect(),
        })
    }
}

#[cfg(feature = "database")]
#[async_trait]
impl StorageBackend for DatabaseStorage {
    async fn store(&self, result: &ScanResult) -> Result<(), good_mitm_passive_scan::ScanError> {
        debug!("Storing scan result to database: {}", result.rule_id);

        let metadata_json = serde_json::to_string(&result.metadata)
            .map_err(|e| good_mitm_passive_scan::ScanError::StorageError(e.to_string()))?;

        let sql = r#"
            INSERT INTO scan_results
            (id, rule_id, rule_name, severity, url, host, path, method, matched_content, evidence, created_at, metadata)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#;

        sqlx::query(sql)
            .bind(&result.id)
            .bind(&result.rule_id)
            .bind(&result.rule_name)
            .bind(format!("{:?}", result.severity))
            .bind(&result.url)
            .bind(&result.host)
            .bind(&result.path)
            .bind(&result.method)
            .bind(&result.matched_content)
            .bind(&result.evidence)
            .bind(result.timestamp)
            .bind(&metadata_json)
            .execute(&self.pool)
            .await
            .map_err(|e| good_mitm_passive_scan::ScanError::StorageError(e.to_string()))?;

        debug!("Successfully stored scan result: {}", result.id);

        Ok(())
    }

    async fn batch_store(
        &self,
        results: &[ScanResult],
    ) -> Result<(), good_mitm_passive_scan::ScanError> {
        if results.is_empty() {
            return Ok(());
        }

        debug!("Batch storing {} scan results to database", results.len());

        let mut tx = self
            .pool
            .begin()
            .await
            .map_err(|e| good_mitm_passive_scan::ScanError::StorageError(e.to_string()))?;

        let sql = r#"
            INSERT INTO scan_results
            (id, rule_id, rule_name, severity, url, host, path, method, matched_content, evidence, created_at, metadata)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#;

        for result in results {
            let metadata_json = serde_json::to_string(&result.metadata)
                .map_err(|e| good_mitm_passive_scan::ScanError::StorageError(e.to_string()))?;

            sqlx::query(sql)
                .bind(&result.id)
                .bind(&result.rule_id)
                .bind(&result.rule_name)
                .bind(format!("{:?}", result.severity))
                .bind(&result.url)
                .bind(&result.host)
                .bind(&result.path)
                .bind(&result.method)
                .bind(&result.matched_content)
                .bind(&result.evidence)
                .bind(result.timestamp)
                .bind(&metadata_json)
                .execute(&mut *tx)
                .await
                .map_err(|e| good_mitm_passive_scan::ScanError::StorageError(e.to_string()))?;
        }

        tx.commit()
            .await
            .map_err(|e| good_mitm_passive_scan::ScanError::StorageError(e.to_string()))?;

        debug!("Successfully batch stored {} scan results", results.len());

        Ok(())
    }

    async fn flush(&self) -> Result<(), good_mitm_passive_scan::ScanError> {
        // Database storage doesn't need explicit flushing
        Ok(())
    }
}

/// 数据库中存储的扫描结果
#[cfg(feature = "database")]
#[derive(Debug, Clone, sqlx::FromRow)]
pub struct StoredScanResult {
    pub id: String,
    pub rule_id: String,
    pub rule_name: String,
    pub severity: String,
    pub url: String,
    pub host: String,
    pub path: String,
    pub method: String,
    pub matched_content: Option<String>,
    pub evidence: Option<String>,
    pub created_at: DateTime<Utc>,
    pub metadata: String,
}

/// 扫描统计信息
#[cfg(feature = "database")]
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct ScanStatistics {
    pub total_results: u64,
    pub by_severity: std::collections::HashMap<String, i64>,
}

// Stub 实现（当 database feature 未启用时）
#[cfg(not(feature = "database"))]
pub struct DatabaseStorage;

#[cfg(not(feature = "database"))]
impl DatabaseStorage {
    pub async fn new(_config: DatabaseConfig) -> Result<Self, anyhow::Error> {
        Err(anyhow::anyhow!(
            "Database storage is not enabled. Please compile with --features database"
        ))
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_database_config_default() {
        let config = DatabaseConfig::default();
        assert_eq!(config.db_type, "sqlite");
        assert_eq!(config.max_connections, 10);
    }
}
